package diploma.kisel.modelas.reports;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;

import diploma.kisel.modelas.service.StaticHelper;

import net.sf.jasperreports.engine.JRException;
import net.sf.jasperreports.engine.JRExporterParameter;
import net.sf.jasperreports.engine.JRReportFont;
import net.sf.jasperreports.engine.JRResultSetDataSource;
import net.sf.jasperreports.engine.JasperCompileManager;
import net.sf.jasperreports.engine.JasperExportManager;
import net.sf.jasperreports.engine.JasperFillManager;
import net.sf.jasperreports.engine.JasperPrint;
import net.sf.jasperreports.engine.JasperReport;
import net.sf.jasperreports.engine.design.JRDesignReportFont;
import net.sf.jasperreports.engine.export.oasis.JROdsExporter;
import net.sf.jasperreports.engine.export.oasis.JROdtExporter;
import net.sf.jasperreports.engine.export.ooxml.JRDocxExporter;
import net.sf.jasperreports.engine.export.JRRtfExporter;
import net.sf.jasperreports.engine.export.JRXlsExporter;
import net.sf.jasperreports.engine.export.JRXlsExporterParameter;

public class JDBCReport {

	private final static String driver = "com.mysql.jdbc.Driver";
	private final static String url = "jdbc:mysql://localhost:3306/db_model_as_work";
	private final static String user = "root";
	private final static String password = "12345635";

	// private String jrxmlFile = "/ComputerReport.jrxml";

	private static Connection connection = null;
	private static ResultSet resultSet = null;
	private static PreparedStatement statement = null;

	private final int numberOfServerQuery = 1;
	private final int numberOfWpQuery = 2;
	private int number;
	private List<Object> params;

	public void writeRepot(File file, ReportsType reportsType, int number,
			List<Object> params) {
		try {
			openConnection();
			String ext = StaticHelper.getExtension(file.getName());
			this.number = number;
			this.params = params;
			switch (reportsType) {
			case AllPC:
				writeReportToFile(ext, file, ReportsConst.queryFindAllPC,
						ReportsConst.jrxmlComputers);
				break;
			case AllDevices:
				writeReportToFile(ext, file, ReportsConst.queryFindAllDevice,
						ReportsConst.jrxmlDevices);
				break;
			case AllProgram:
				writeReportToFile(ext, file, ReportsConst.queryFindAllProgram,
						ReportsConst.jrxmlProgram);
				break;
			case Servers:
				writeReportToFile(ext, file,
						ReportsConst.queryFindServersByConcept,
						ReportsConst.jrxmlServers);
				break;
			case Workplace:
				writeReportToFile(ext, file, ReportsConst.queryFindWpByConcept,
						ReportsConst.jrxmlWorkplaces);
			}

		} catch (Exception e) {

			e.printStackTrace();
		} finally {
			try {
				closeConnection();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	private void writeReportToFile(String ext, File file, String query,
			String jrxmlFile) throws Exception {
		switch (ext) {
		case "docx":
			writeReportToDocx(file, query, jrxmlFile);
			break;
		case "doc":
			writeReportToDoc(file, query, jrxmlFile);
			break;
		case "odt":
			writeReportToOdt(file, query, jrxmlFile);
			break;
		case "ods":
			writeReportToOds(file, query, jrxmlFile);
			break;
		case "pdf":
			writeReportToPdf(file, query, jrxmlFile);
		}
	}

	public static Connection openConnection() throws Exception {
		Class.forName(driver);
		connection = DriverManager.getConnection(url, user, password);
		return connection;
	}

	public static void closeConnection() throws Exception {
		// resultSet.close();
		// statement.close();
		connection.close();
	}

	private JasperPrint getJasperPrint(String query, String jrxmlFile)
			throws Exception {
		statement = connection.prepareStatement(query);
		if (number == numberOfServerQuery)
			setParametersForServerQuery();
		if (number == numberOfWpQuery)
			setParametersForWpQuery();
		resultSet = statement.executeQuery();
		JasperReport jasperReport = JasperCompileManager
				.compileReport(JDBCReport.class.getResourceAsStream(jrxmlFile));
		JasperPrint jasperPrint = JasperFillManager.fillReport(jasperReport,
				new HashMap(), new JRResultSetDataSource(resultSet));
		return jasperPrint;
	}

	private void setParametersForWpQuery() throws Exception {
		statement.setInt(1, (int) params.get(0));
		statement.setInt(2, (int) params.get(0));
		statement.setInt(3, (int) params.get(0));
		statement.setInt(4, (int) params.get(0));
		statement.setInt(5, (int) params.get(0));
	}

	private void setParametersForServerQuery() throws Exception {
		statement.setInt(1, (int) params.get(0));
		statement.setInt(2, (int) params.get(0));
		statement.setInt(3, (int) params.get(0));
	}

	private void writeReportToDocx(File file, String query, String jrxmlFile)
			throws Exception {

		JasperPrint jasperPrint = getJasperPrint(query, jrxmlFile);
		File destFile = file;

		JRDocxExporter exporter = new JRDocxExporter();

		exporter.setParameter(JRExporterParameter.JASPER_PRINT, jasperPrint);
		exporter.setParameter(JRExporterParameter.OUTPUT_FILE_NAME,
				destFile.toString());

		exporter.exportReport();
	}

	private void writeReportToDoc(File file, String query, String jrxmlFile)
			throws Exception {
		JasperPrint jasperPrint = getJasperPrint(query, jrxmlFile);

		File destFile = file;

		JRRtfExporter exporter = new JRRtfExporter();

		exporter.setParameter(JRExporterParameter.JASPER_PRINT, jasperPrint);
		exporter.setParameter(JRExporterParameter.OUTPUT_FILE_NAME,
				destFile.toString());

		exporter.exportReport();

	}

	private void writeReportToOdt(File file, String query, String jrxmlFile)
			throws Exception {
		JasperPrint jasperPrint = getJasperPrint(query, jrxmlFile);

		File destFile = file;

		JROdtExporter exporter = new JROdtExporter();

		exporter.setParameter(JRExporterParameter.JASPER_PRINT, jasperPrint);
		exporter.setParameter(JRExporterParameter.OUTPUT_FILE_NAME,
				destFile.toString());

		exporter.exportReport();

	}

	private void writeReportToOds(File file, String query, String jrxmlFile)
			throws Exception {
		JasperPrint jasperPrint = getJasperPrint(query, jrxmlFile);

		File destFile = file;

		JROdsExporter exporter = new JROdsExporter();

		exporter.setParameter(JRExporterParameter.JASPER_PRINT, jasperPrint);
		exporter.setParameter(JRExporterParameter.OUTPUT_FILE_NAME,
				destFile.toString());
		exporter.setParameter(JRXlsExporterParameter.IS_ONE_PAGE_PER_SHEET,
				Boolean.TRUE);

		exporter.exportReport();
	}

	private void writeReportToPdf(File file, String query, String jrxmlFile)
			throws Exception {
		JasperPrint jasperPrint = getJasperPrint(query, jrxmlFile);
		File destFile = file;

		JRReportFont font = new JRDesignReportFont();
		font.setPdfFontName("/fonts/times.ttf");
		font.setPdfEncoding("Cp1251");
		font.setPdfEmbedded(true);
		jasperPrint.setDefaultFont(font);
		JasperExportManager.exportReportToPdfFile(jasperPrint,
				destFile.toString());
	}
}
